package movEat.Kernel.DataBase;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import movEat.Definitions.Decade;
import movEat.Definitions.Genre;
import movEat.Definitions.MovieDB;
import movEat.Definitions.Pair;
import movEat.Definitions.Rating;
import movEat.Definitions.TVshowDB;
import movEat.Generic.ConfigInfo;
import movEat.IGui.OrderDetails.*;

import movEat.Kernel.Import.Utils;

public class DataBaseManaging {

	private static ArrayList<ManagedConnection> connections = null;

	private static int connectionsCounter = 0;

	/* methods for querying the dataBase for the dishes */

	public static ArrayList<Pair<String, Integer>> getAppetizers(IAppetizerOrderDetails appetizerOrder) throws SQLException {

		String query = buildQuery(appetizerOrder);
		ArrayList<Pair<String, Integer>> result = executeQuery(query, "tv_name");
		return result;
	}

	public static ArrayList<Pair<String, Integer>> getMainCourse(IMainCourseOrderDetails MainCourseOrder) throws SQLException {
		String query = buildQuery(MainCourseOrder);
		ArrayList<Pair<String, Integer>> result = executeQuery(query,
				"movie_name");
		return result;
	}

	public static ArrayList<Pair<String, Integer>> getDessert(
			int[] dessertOrderDetails) throws SQLException {
		String query = buildQuery(dessertOrderDetails);
		ArrayList<Pair<String, Integer>> result = executeQuery(query,
				"movie_name");

		return result;
	}

	public static ArrayList<Pair<String, Integer>> getDrinks(
			IDrinksOrderDetails drinksOrder) throws SQLException {
		String query = buildQuery(drinksOrder);
		ArrayList<Pair<String, Integer>> result = executeQuery(query,
				"movie_name");
		return result;

	}

	private static ArrayList<Pair<String, Integer>> executeQuery(String query,
			String column) throws SQLException {

		ArrayList<Pair<String, Integer>> result = null;

		ManagedConnection Mconn = getConnection();
		Statement statement = Mconn.getStatement();
		ResultSet resultSet = statement.executeQuery(query);

		Mconn.setAvailability(true);

		result = ResultSetToListOfPairs(resultSet, column);

		return result;
	}

	// depends on what is written on decade in the movies table
	private static String buildQuery(IAppetizerOrderDetails appetizerOrder) {

		String query = "";

		query += "SELECT tv_name, stars FROM tv_shows ";

		int price = appetizerOrder.getPrice();
		double stars = price;

		String condition = "";
		condition += "WHERE tv_shows.decade = "
				+ DecadetoInt(appetizerOrder.GetDecade()) + " AND stars <="
				+ (stars + 5) + " AND stars>=" + (stars - 5);

		query += condition;

		return query;

	}

	private static String buildQuery(IMainCourseOrderDetails mainCourseOrder) {

		if (mainCourseOrder.getActor() != null)
			return queryByActor(mainCourseOrder);

		return queryByOther(mainCourseOrder);

	}

	private static String queryByOther(IMainCourseOrderDetails mainCourseOrder) {
		String query = "";

		query += "SELECT movie_name, stars FROM movies,genres_movies ";

		String conditions = "WHERE ";

		String genreCondition = "";
		genreCondition += "movies.movie_id = genres_movies.movie_id AND genres_movies.genre_id="
				+ genresToInt(mainCourseOrder.getGenre());
		conditions += genreCondition;

		if (mainCourseOrder.getCountry() != null) {
			String countryCondition = "";
			countryCondition += " AND country='" + mainCourseOrder.getCountry()
					+ "' ";

			conditions += countryCondition;
		}

		int price = mainCourseOrder.getPrice();
		double stars = price;

		conditions += " AND stars>=" + (stars - 5) + " AND stars<="
				+ (stars + 5);

		query += conditions;

		return query;

	}

	private static String queryByActor(IMainCourseOrderDetails mainCourseOrder) {

		String query = "";

		query += "SELECT movie_name,stars FROM movies, actors_movies, actors ";

		String conditions = "WHERE ";

		String actorCondition = "";
		actorCondition += "movies.movie_id = actors_movies.movie_id AND actors.actor_id = actors_movies.actor_id AND "
				+ "actor_imdb_name = '" + Utils.escapeApostrophe(mainCourseOrder.getActor()) + "' ";

		query += conditions + actorCondition;

		return query;

	}

	private static String buildQuery(IDrinksOrderDetails drinksOrder) {

		String query = "";

		query += "SELECT movie_name, stars FROM movies, movies_drinks ";

		int price = drinksOrder.getPrice();
		double stars = price;

		String condition = "";
		condition += "WHERE movies.movie_id = movies_drinks.movie_id AND movies_drinks.drink_id = "
				+ RatingtoInt(drinksOrder.getRating());

		condition += " AND movies.stars >= " + (stars - 5)
				+ " AND movies.stars<=" + (stars + 5);

		query += condition;

		return query;

	}

	private static String buildQuery(int[] dessertOrder) {

		String query = "";

		query += "SELECT distinct(movie_name), stars FROM movies WHERE movie_id="
				+ dessertOrder[0]
				+ " OR movie_id="
				+ dessertOrder[1]
				+ " OR movie_id=" + dessertOrder[2];

		return query;

	}

	private static ArrayList<Pair<String, Integer>> ResultSetToListOfPairs(
			ResultSet resultSet, String column) throws SQLException {

		ArrayList<Pair<String, Integer>> result = new ArrayList<Pair<String, Integer>>();

		if (resultSet == null)
			return result;

		while (resultSet.next()) {
			String name = resultSet.getString(column);
			int stars = resultSet.getInt("stars");
			result.add(new Pair<String, Integer>(name, stars));
		}

		return result;

	}

	/* initializing and terminate DB */
	public static void ShutDown() throws SQLException {

		if (connections != null) {
			for (ManagedConnection con : connections) {
				con.shutDown();
			}
		}

	}

	public static void init(ConfigInfo configInfo) {

		ManagedConnection.setDBUserName(configInfo.getDbUserName());
		ManagedConnection.setDBPassword(configInfo.getDbPassword());
		ManagedConnection.setDbName(configInfo.getSchema());
		ManagedConnection.setPort(configInfo.getPort());
		connections = new ArrayList<ManagedConnection>();

	}

	private synchronized static ManagedConnection getConnection()
			throws SQLException {
		ManagedConnection con = getAvailableConnection();
		con.setAvailability(false);
		return con;
	}

	private static ManagedConnection getAvailableConnection()
			throws SQLException {
		for (ManagedConnection con : connections) {
			if (con.isAvailable()) {
//				System.out
//						.println(Thread.currentThread()
//								+ " is using an exin exists ction. number of connections is "
//								+ connectionsCounter);
				return con;
			}
		}

		ManagedConnection newConnection = new ManagedConnection();
		connections.add(newConnection);
		connectionsCounter++;
//		System.out.println(Thread.currentThread()
//				+ " added a connection. number of connections is "
//				+ connectionsCounter);

		return newConnection;
	}

	// gets the max movie_id in the table movies
	private static int getMaxIdMoviesTable() throws SQLException {
		return getMaxIdTable("movie_id", "movies");
	}

	// gets the max actor_id in table actors
	private static int getMaxIdActorsTable() throws SQLException {
		return getMaxIdTable("actor_id", "actors");
	}

	// gets the max of field in table
	private static int getMaxIdTable(String field, String table)
			throws SQLException {
		ManagedConnection Mconn = null;
		int result = 0;
		Mconn = getConnection();
		Statement stmt = Mconn.getStatement();
		ResultSet rs = stmt.executeQuery("SELECT MAX(" + field + ") max FROM "
				+ table);
		rs.next();
		result = rs.getInt("max");
		Mconn.setAvailability(true);
		return result + 1;
	}

	// a public static function which adds to the table actors the list of
	// actors it receives as a variable
	// this function calls the private function buildActorsTable
	public static void addToActorsTable(List<String> actors, int first_actress)
			throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildActorsTable(actors, Mconn, getMaxIdActorsTable(), first_actress);
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of actors and their information, a
	// ManagedConnection
	// and the highest currently used actor_id in the actors table
	// the function returns a map with the keys being the actors names and the
	// values their actor_id in the actors table
	private static void buildActorsTable(List<String> actors,
			ManagedConnection Mconn, int start, int first_actress)
			throws SQLException {
		int i = start, size = actors.size() + start;
		String name;
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO actors(actor_name,actor_imdb_name,actor_id) VALUES(?,?,?)");
		Mconn.setAutoCommit(false);

		while (i < size) {
			if (i % 50000 == 0) {
				pstmt.executeBatch();
				Mconn.commit();
				pstmt.clearBatch();
			}
			name = actors.get(i - start);
			pstmt.setString(1, Utils.flipName(name));
			if ((i - start) >= first_actress)
				pstmt.setString(2, Utils.turnToFemale(name));
			else
				pstmt.setString(2, name);
			pstmt.setInt(3, i);
			pstmt.addBatch();

			i++;
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// used only for first import
	// a public static function which adds to the table actors the list of
	// actors it receives as a variable
	// this function calls the private function buildActorsTable
	public static Map<String, Integer> addToActorsTable2(List<String> actors,
			int first_actress) throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		Map<String, Integer> result = buildActorsTable2(actors, Mconn,
				getMaxIdActorsTable(), first_actress);
		Mconn.setAvailability(true);

		return result;
	}

	// used only for first import
	// a private function which gets a list of actors and their information, a
	// ManagedConnection
	// and the highest currently used actor_id in the actors table
	// the function returns a map with the keys being the actors names and the
	// values their actor_id in the actors table
	private static Map<String, Integer> buildActorsTable2(List<String> actors,
			ManagedConnection Mconn, int start, int first_actress)
			throws SQLException {
		int i = start, size = actors.size() + start;
		String name;
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO actors(actor_name,actor_imdb_name,actor_id) VALUES(?,?,?)");
		Mconn.setAutoCommit(false);

		String name_entered;
		Map<String, Integer> result = new HashMap<String, Integer>();
		while (i < size) {
			if (i % 50000 == 0) {
				pstmt.executeBatch();
				Mconn.commit();
				pstmt.clearBatch();
			}
			name = actors.get(i - start);
			pstmt.setString(1, Utils.flipName(name));
			if ((i - start) >= first_actress)
				name_entered = Utils.turnToFemale(name);
			else
				name_entered = name;
			pstmt.setString(2, name_entered);
			pstmt.setInt(3, i);
			pstmt.addBatch();

			result.put(name_entered, i);

			i++;
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);

		return result;
	}

	// a public static function which adds to the table movies the list of
	// movies it receives as a variable
	// this function calls the private function buildMoviesTable
	public static void addToMoviesTable(Map<String, MovieDB> movies)
			throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildMoviesTable(movies, Mconn, getMaxIdMoviesTable());
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of movies and their information, a
	// ManagedConnection
	// and the highest currently used movie_id in the movies table
	// the function returns a map with the keys being the movies names and the
	// values their movie_id in the movies table
	private static void buildMoviesTable(Map<String, MovieDB> movies,
			ManagedConnection Mconn, int start) throws SQLException {
		int i = start;
		String name;
		MovieDB movie;
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO movies(movie_name,imdb_movie_name,stars,country,year,movie_id) VALUES(?,?,?,?,?,?)");
		Mconn.setAutoCommit(false);

		for (Entry<String, MovieDB> movie_entry : movies.entrySet()) {
			if (i % 100000 == 0) {
				pstmt.executeBatch();
				Mconn.commit();
				pstmt.clearBatch();
			}
			movie = movie_entry.getValue();
			name = movie.getImdbName();
			pstmt.setString(1, name);
			pstmt.setString(2, name);
			if ((int) movie.getStars() == 0)
				movie.setStars(Math.random() * 10);
			pstmt.setInt(3, (int) (10 * movie.getStars()));
			pstmt.setString(4, movie.getCountry());
			pstmt.setInt(5, movie.getYear());
			pstmt.setInt(6, i);
			pstmt.addBatch();

			movie.setId(i);

			i++;
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// a public static function which adds to the table TV_shows the list of tv
	// shows it receives as a variable
	// this function calls the private function buildTVshowsTable
	public static void addToTVshowsTable(Map<String, TVshowDB> shows)
			throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildTVshowsTable(shows, Mconn);
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of tv shows and their information, a
	// ManagedConnection
	// and the highest currently used tv_id in the tv_shows table
	private static void buildTVshowsTable(Map<String, TVshowDB> shows,
			ManagedConnection Mconn) throws SQLException {
		int decade, i = 1;
		String name;
		TVshowDB show;
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO TV_shows(tv_name,tv_imdb_name,stars,decade) VALUES(?,?,?,?)");
		Mconn.setAutoCommit(false);

		for (Entry<String, TVshowDB> show_entry : shows.entrySet()) {
			if (i % 100000 == 0) {
				pstmt.executeBatch();
				Mconn.commit();
				pstmt.clearBatch();
			}
			show = show_entry.getValue();
			name = show.getImdbName();
			pstmt.setString(1, name);
			pstmt.setString(2, name);
			if (show.getStars() == 0)
				show.setStars((int) (Math.random() * 10));
			pstmt.setInt(3, show.getStars());
			decade = DecadetoInt(show.getDecade());
			pstmt.setInt(4, decade);
			pstmt.addBatch();

			i++;
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// a public static function which adds to the table actors_movies all the
	// actors which appear in the movies
	// in the list movies
	// this function calls the private function buildActorsMoviesTable
	public static void addToDrinksTable(Map<String, MovieDB> movies)
			throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildDrinksTable(movies, Mconn);
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of movies and their information,
	// a ManagedConnection and a map which has the movie_id for every movie
	// the function adds all the connection between the movies and the drinks
	// they represent
	private static void buildDrinksTable(Map<String, MovieDB> movies,
			ManagedConnection Mconn) throws SQLException {
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO movies_drinks(movie_id,drink_id) VALUES(?,?)");
		Mconn.setAutoCommit(false);
		int rating, i = 0;
		MovieDB movie;

		for (Entry<String, MovieDB> movie_entry : movies.entrySet()) {
			movie = movie_entry.getValue();
			if (movie.getIsDrink()) {
				pstmt.setInt(1, movie.getId());
				// 1 represents adults, 2 teens and 3 kids
				rating = RatingtoInt(movie.getRating());
				pstmt.setInt(2, rating);
				pstmt.addBatch();
				if (i++ % 50000 == 0) {
					pstmt.executeBatch();
					Mconn.commit();
					pstmt.clearBatch();
				}

			}
		}
		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// a public static function which adds to the table actors_movies all the
	// actors which appear in the movies
	// in the list movies
	// this function calls the private function buildActorsMoviesTable
	public static void addToActorsMoviesTable(Map<String, MovieDB> movies,
			List<String> actors, int first_actress) throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildActorsMoviesTable(movies, actors, first_actress, Mconn);
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of movies and their information with
	// their actors,
	// a ManagedConnection and 2 maps which have the actor_id for every actor
	// and the movie_id for every movie
	// the function adds all the connection between the movies and the actors
	private static void buildActorsMoviesTable(Map<String, MovieDB> movies,
			List<String> actors, int first_actress, ManagedConnection Mconn)
			throws SQLException {
		Statement st = Mconn.getStatement();
		Mconn.setAutoCommit(false);
		int actors_size, i = 0;
		MovieDB movie;
		List<Integer> actors_list;
		String actor_imdb_name;

		for (Entry<String, MovieDB> movie_entry : movies.entrySet()) {
			movie = movie_entry.getValue();
			actors_list = movie.getActor_nums();
			if (actors_list != null) {
				actors_size = actors_list.size();
				for (int j = 0; j < actors_size; j++) {

					if (i % 50000 == 0) {
						st.executeBatch();
						Mconn.commit();
						st.clearBatch();
					}
					if (actors_list.get(j) >= first_actress)
						actor_imdb_name = Utils.escapeApostrophe(Utils
								.turnToFemale(actors.get(actors_list.get(j))));
					else
						actor_imdb_name = Utils.escapeApostrophe(actors
								.get(actors_list.get(j)));
					st.addBatch("INSERT INTO actors_movies(movie_id,actor_id) VALUES("
							+ movie.getId()
							+ ",(SELECT actor_id FROM actors WHERE actor_imdb_name='"
							+ actor_imdb_name + "'))");
					i++;
				}
			}
		}

		st.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// used only for first import
	// a public static function which adds to the table actors_movies all the
	// actors which appear in the movies
	// in the list movies
	// this function calls the private function buildActorsMoviesTable
	public static void addToActorsMoviesTable2(Map<String, MovieDB> movies,
			List<String> actors, int first_actress,
			Map<String, Integer> actors_id) throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildActorsMoviesTable2(movies, actors, first_actress, actors_id, Mconn);
		Mconn.setAvailability(true);

	}

	// used only for first import
	// a private function which gets a list of movies and their information with
	// their actors,
	// a ManagedConnection and 2 maps which have the actor_id for every actor
	// and the movie_id for every movie
	// the function adds all the connection between the movies and the actors
	private static void buildActorsMoviesTable2(Map<String, MovieDB> movies,
			List<String> actors, int first_actress,
			Map<String, Integer> actors_id, ManagedConnection Mconn)
			throws SQLException {
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO actors_movies(movie_id,actor_id) VALUES(?,?)");
		Mconn.setAutoCommit(false);
		int actors_size, i = 0;
		MovieDB movie;
		List<Integer> actors_list;
		String actor_imdb_name;

		for (Entry<String, MovieDB> movie_entry : movies.entrySet()) {
			movie = movie_entry.getValue();
			actors_list = movie.getActor_nums();
			if (actors_list != null) {
				actors_size = actors_list.size();
				for (int j = 0; j < actors_size; j++) {

					if (i % 5000 == 0) {
						pstmt.executeBatch();
						Mconn.commit();
						pstmt.clearBatch();
					}
					if (actors_list.get(j) >= first_actress)
						actor_imdb_name = Utils.turnToFemale(actors
								.get(actors_list.get(j)));
					else
						actor_imdb_name = actors.get(actors_list.get(j));
					pstmt.setInt(1, movie.getId());
					pstmt.setInt(2, actors_id.get(actor_imdb_name));
					pstmt.addBatch();
					i++;
				}
			}
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// a public static function which adds to the table genres_movies all the
	// genres for the movies received in the list of movies
	// this function calls the private function buildGenressMoviesTable
	public static void addToGenresMoviesTable(Map<String, MovieDB> movies)
			throws SQLException {
		ManagedConnection Mconn = null;
		Mconn = getConnection();
		buildGenresMoviesTable(movies, Mconn);
		Mconn.setAvailability(true);
	}

	// a private function which gets a list of movies and their information with
	// their genres,
	// a ManagedConnection and a map which has the movie_id for every movie
	// the function adds all the connection between the movies and the genres
	private static void buildGenresMoviesTable(Map<String, MovieDB> movies,
			ManagedConnection Mconn) throws SQLException {
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO genres_movies(movie_id,genre_id) VALUES(?,?)");
		Mconn.setAutoCommit(false);
		int size_genres, i = 0;
		MovieDB movie;

		for (Entry<String, MovieDB> movie_entry : movies.entrySet()) {
			movie = movie_entry.getValue();
			if (movie.getGenres() != null) {
				size_genres = movie.getGenres().size();
				for (int j = 0; j < size_genres; j++) {
					pstmt.setInt(1, movie.getId());
					pstmt.setInt(2, genresToInt(movie.getGenres().get(j)));
					pstmt.addBatch();
				}
			} else {
				pstmt.setInt(1, movie.getId());
				pstmt.setInt(2, genresToInt(Genre.OTHER));
				pstmt.addBatch();
			}
			if (i++ % 50000 == 0) {
				pstmt.executeBatch();
				Mconn.commit();
				pstmt.clearBatch();
			}
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);
	}

	// a private function which receives a Genre and returns it genre_id in the
	// genres table
	private static int genresToInt(Genre genre) {
		switch (genre) {

		case ACTION:
			return 1;
		case ADVENTURE:
			return 2;
		case ADULT:
			return 3;
		case ANIMATION:
			return 4;
		case BIOGRAPHY:
			return 5;
		case COMEDY:
			return 6;
		case CRIME:
			return 7;
		case DOCUMENTARY:
			return 8;
		case DRAMA:
			return 9;
		case FANTASY:
			return 10;
		case FAMILY:
			return 11;
		case FILM_NOIR:
			return 12;
		case GAME_SHOW:
			return 13;
		case HISTORY:
			return 14;
		case HORROR:
			return 15;
		case LIFESTYLE:
			return 16;
		case MUSIC:
			return 17;
		case MUSICAL:
			return 18;
		case MYSTERY:
			return 19;
		case NEWS:
			return 20;
		case REALITY_TV:
			return 21;
		case ROMANCE:
			return 22;
		case SCI_FI:
			return 23;
		case SHORT:
			return 24;
		case SPORT:
			return 25;
		case TALK_SHOW:
			return 26;
		case THRILLER:
			return 27;
		case WAR:
			return 28;
		case WESTERN:
			return 29;
		case OTHER:
			return 30;
		}

		return 0;
	}

	// a private function which receives a Genre and returns it genre_id in the
	// genres table
	private static Genre IntToGenre(int n) {
		switch (n) {

		case 1:
			return Genre.ACTION;
		case 2:
			return Genre.ADVENTURE;
		case 3:
			return Genre.ADULT;
		case 4:
			return Genre.ANIMATION;
		case 5:
			return Genre.BIOGRAPHY;
		case 6:
			return Genre.COMEDY;
		case 7:
			return Genre.CRIME;
		case 8:
			return Genre.DOCUMENTARY;
		case 9:
			return Genre.DRAMA;
		case 10:
			return Genre.FANTASY;
		case 11:
			return Genre.FAMILY;
		case 12:
			return Genre.FILM_NOIR;
		case 13:
			return Genre.GAME_SHOW;
		case 14:
			return Genre.HISTORY;
		case 15:
			return Genre.HORROR;
		case 16:
			return Genre.LIFESTYLE;
		case 17:
			return Genre.MUSIC;
		case 18:
			return Genre.MUSICAL;
		case 19:
			return Genre.MYSTERY;
		case 20:
			return Genre.NEWS;
		case 21:
			return Genre.REALITY_TV;
		case 22:
			return Genre.ROMANCE;
		case 23:
			return Genre.SCI_FI;
		case 24:
			return Genre.SHORT;
		case 25:
			return Genre.SPORT;
		case 26:
			return Genre.TALK_SHOW;
		case 27:
			return Genre.THRILLER;
		case 28:
			return Genre.WAR;
		case 29:
			return Genre.WESTERN;
		case 30:
			return Genre.OTHER;
		}

		// should never get here
		return null;
	}

	// return a list of actors who act in at least numOfMoviesPerActor
	public static ArrayList<Pair<String, Integer>> getBestActors(
			int numOfMoviesPerActor) throws SQLException {

		ManagedConnection mConn = getConnection();

		String query = "SELECT actor_name FROM ";
		String nestedQuery = "(select actor_name,actors_movies.actor_id ,count(*) as numOfMovies "
				+ "FROM actors_movies,actors WHERE actors.actor_id = actors_movies.actor_id "
				+ "group by actors_movies.actor_id)as x ";

		query += nestedQuery;
		query += "where numOfMovies>" + numOfMoviesPerActor;

		ResultSet resultSet = mConn.getStatement().executeQuery(query);
		ArrayList<Pair<String, Integer>> res = ResultSetToListOfPairs(
				resultSet, "actor_name");

		return res;

	}

	// adds a new movie to the database
	// returns true if added the new movie and the new movie name is unique
	// if the movie name is not uniwue it returns false and doesn't enter the
	// movie to the database
	public static Boolean addNewMovie(MovieDB movie) throws SQLException {
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		String query = "SELECT movie_id FROM movies WHERE imdb_movie_name='"
				+ Utils.escapeApostrophe(movie.getImdbName()) + "'";
		ResultSet rs = st.executeQuery(query);
		if (rs.next()) {
			Mconn.setAvailability(true);
			return false;
		}

		int size_genres, movie_id = getMaxIdMoviesTable();
		query = "INSERT INTO movies (movie_name, imdb_movie_name,stars,country,year,movie_id) VALUES ('"
				+ movie.getImdbName()
				+ "', '"
				+ movie.getImdbName()
				+ "', '"
				+ movie.getStars()
				+ "', '"
				+ movie.getCountry()
				+ "', '"
				+ movie.getYear() + "', '" + movie_id + "')";
		Mconn = getConnection();
		st.execute(query);
		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO genres_movies(movie_id,genre_id) VALUES(?,?)");
		if (movie.getGenres() != null) {
			size_genres = movie.getGenres().size();
			for (int j = 0; j < size_genres; j++) {
				pstmt.setInt(1, movie_id);
				pstmt.setInt(2, genresToInt(movie.getGenres().get(j)));
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		}
		if (movie.getIsDrink()) {
			int rating = RatingtoInt(movie.getRating());
			query = "INSERT INTO movies_drinks(movie_id,drink_id) VALUES('"
					+ movie_id + "', '" + rating + "')";
			st.execute(query);
		}
		Mconn.setAvailability(true);
		return true;
	}

	// adds a new actor to the database
	// returns true if the name was not in the imdb_name column
	// else returns false and didn't insert the new actor
	public static Boolean addNewActor(String name) throws SQLException {
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		String query = "SELECT actor_id FROM actors WHERE actor_imdb_name='"
				+ Utils.escapeApostrophe(name) + "'";
		ResultSet rs = st.executeQuery(query);
		if (rs.next()) {
			Mconn.setAvailability(true);
			return false;
		}
		query = "INSERT INTO actors (actor_name, actor_imdb_name) VALUES ('"
				+ name + "', '" + name + "')";
		st.execute(query);
		Mconn.setAvailability(true);
		return true;
	}

	// adds a new tv show to the database
	// returns true if the name was not in the imdb_name column
	// else returns false and didnt insert the new show
	public static Boolean addNewShow(TVshowDB show) throws SQLException {
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		String query = "SELECT tv_id FROM tv_shows WHERE tv_imdb_name='"
				+ Utils.escapeApostrophe(show.getImdbName()) + "'";
		ResultSet rs = st.executeQuery(query);
		if (rs.next()) {
			Mconn.setAvailability(true);
			return false;
		}
		int decade = DecadetoInt(show.getDecade());
		query = "INSERT INTO tv_shows (tv_name, tv_imdb_name,stars,decade) VALUES ('"
				+ show.getImdbName()
				+ "', '"
				+ show.getImdbName()
				+ "', '"
				+ show.getStars() + "', '" + decade + "')";
		st.execute(query);
		Mconn.setAvailability(true);
		return true;
	}

	// gets a name and finds all the actors whos name conatains this name as
	// well
	// returns the map of those actors with their id's
	public static Map<Integer, String> findActor(String first, String last)
			throws SQLException {
		String query = "SELECT actor_name,actor_id FROM actors WHERE LOWER(actor_name) LIKE'%"
				+ Utils.escapeApostrophe(first).toLowerCase()
				+ "%"
				+ Utils.escapeApostrophe(last).toLowerCase() + "%'";
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		ResultSet rs = st.executeQuery(query);
		Mconn.setAvailability(true);
		return ResultSetToActors(rs);
	}

	// gets the result set from findActor and turns the information into a map
	// of id's and the actor names they represent
	private static Map<Integer, String> ResultSetToActors(ResultSet rs)
			throws SQLException {
		String name;
		int id;
		Map<Integer, String> result = new HashMap<Integer, String>();
		while (rs.next()) {
			name = rs.getString("actor_name");
			id = rs.getInt("actor_id");
			result.put(id, name);
		}
		return result;
	}

	// finds all the tv shows that contain the name of the tv show in show
	// if there is no decade than the field decade must be null
	// if there is no stars then the stars field must be -1
	public static Map<Integer, TVshowDB> findTVshow(TVshowDB show)
			throws SQLException {
		String decadeQuery;
		int decade = DecadetoInt(show.getDecade());
		if (show.getDecade() == null)
			decadeQuery = "";
		else
			decadeQuery = " AND decade=" + decade;
		String starsQuery;
		if (show.getStars() == -1)
			starsQuery = "";
		else
			starsQuery = " AND stars=" + show.getStars();
		String query = "SELECT tv_id,tv_name, stars, decade FROM tv_shows WHERE LOWER(tv_name) LIKE'%"
				+ Utils.escapeApostrophe(show.getImdbName()).toLowerCase()
				+ "%'" + starsQuery + decadeQuery;
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		ResultSet rs = st.executeQuery(query);
		Mconn.setAvailability(true);

		return ResultSetToTVshowDB(rs);
	}

	// gets a resultSet with the columns tv_name,stars,decade and returns a map
	// of tv shows with the resultset's information
	private static Map<Integer, TVshowDB> ResultSetToTVshowDB(ResultSet rs)
			throws SQLException {
		Map<Integer, TVshowDB> result = new HashMap<Integer, TVshowDB>();
		Decade decade;
		int stars, decadeInt;
		String name;
		while (rs.next()) {
			name = rs.getString("tv_name");
			stars = rs.getInt("stars");
			decadeInt = rs.getInt("decade");
			if (decadeInt == 1)
				decade = Decade.OLD;
			else if (decadeInt == 2)
				decade = Decade.MID;
			else
				decade = Decade.NEW;
			result.put(rs.getInt("tv_id"), new TVshowDB(name, decade, stars));
		}
		return result;

	}

	// gets information about a movie and returns a list of all the movies that
	// have at least
	// all the attributes this movie has besides the name which should appear in
	// somewhere in the movie name
	// uses to function depends if the movie is a drink or not
	// if the movie is a specific drink then isDrink and rating should not be
	// null
	// if the movie is a drink but it doesn't matter which then is drink should
	// be true and rating should be null
	// if the movie is not a drink then isDrink should be false
	// if the movie might be a drink and might not be then isDrink and rating
	// should both be null
	public static Map<Integer, MovieDB> findMovie(MovieDB movie)
			throws SQLException {
		if (movie.getIsDrink() == null) {
			movie.setIsDrink(true);
			movie.setRating(null);
			Map<Integer, MovieDB> result = findMovieWithDrink(movie);
			movie.setIsDrink(false);
			result.putAll(findMovieWithoutDrink(movie));
			return result;
		}
		if (movie.getIsDrink() == true)
			return findMovieWithDrink(movie);
		return findMovieWithoutDrink(movie);
	}

	// gets information about a movie which is a drink and returns a map of all
	// the movies that have at least
	// all the attributes this movie has besides the name which should appear in
	// somewhere in the movie name
	private static Map<Integer, MovieDB> findMovieWithDrink(MovieDB movie)
			throws SQLException {
		String drinkQuery;
		if (movie.getRating() != null) {
			int drink_id = RatingtoInt(movie.getRating());
			drinkQuery = " AND drink_id=" + drink_id;
		} else
			drinkQuery = "";

		String starsQuery;
		if (movie.getStars() == -1)
			starsQuery = "";
		else
			starsQuery = " AND stars=" + movie.getStars();

		String countryQuery;
		if (movie.getCountry() == null)
			countryQuery = "";
		else
			countryQuery = " AND LOWER(country)='"
					+ Utils.escapeApostrophe(movie.getCountry()).toLowerCase()
					+ "'";

		String yearQuery = "";
		if (movie.getYear() != -1)
			starsQuery = " AND year=" + movie.getYear();

		String query = "(SELECT movies.movie_id,movie_name,stars,country,year,drink_id  FROM movies,movies_drinks"
				+ " WHERE movies.movie_id=movies_drinks.movie_id AND LOWER(movie_name) LIKE'%"
				+ Utils.escapeApostrophe(movie.getImdbName()).toLowerCase()
				+ "%'"
				+ drinkQuery
				+ yearQuery
				+ countryQuery
				+ starsQuery
				+ ") AS movies2 ";
		query = "SELECT movies2.movie_id,movie_name,stars,country,year,genre_id,drink_id FROM genres_movies, "
				+ query + "WHERE movies2.movie_id=genres_movies.movie_id";
		if (movie.getGenres() != null) {
			for (Genre genre : movie.getGenres()) {
				query += " AND "
						+ genresToInt(genre)
						+ " IN (SELECT genre_id FROM genres_movies WHERE movie_id=movies2.movie_id)";
			}
		}
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		ResultSet rs = st.executeQuery(query);
		Mconn.setAvailability(true);

		return ResultSetToMovieDBwithDrinks(rs);
	}

	// gets information about a movie which is a not drink and returns a map of
	// all the movies that have at least
	// all the attributes this movie has besides the name which should appear in
	// somewhere in the movie name
	private static Map<Integer, MovieDB> findMovieWithoutDrink(MovieDB movie)
			throws SQLException {
		String starsQuery;
		if (movie.getStars() == -1)
			starsQuery = "";
		else
			starsQuery = " AND stars=" + movie.getStars();

		String countryQuery;
		if (movie.getCountry() == null)
			countryQuery = "";
		else
			countryQuery = " AND LOWER(country)='"
					+ Utils.escapeApostrophe(movie.getCountry()).toLowerCase()
					+ "'";

		String yearQuery = "";
		if (movie.getYear() != -1)
			starsQuery = " AND year=" + movie.getYear();

		String query = "(SELECT movie_id,movie_name,stars,country,year  FROM movies"
				+ " WHERE LOWER(movie_name) LIKE'%"
				+ Utils.escapeApostrophe(movie.getImdbName()).toLowerCase()
				+ "%'"
				+ "AND NOT EXISTS (SELECT movie_id FROM movies_drinks WHERE movies.movie_id=movies_drinks.movie_id)"
				+ // means it is not a drink
				"" + yearQuery + countryQuery + starsQuery + ") AS movies2 ";
		query = "SELECT movies2.movie_id,movie_name,stars,country,year,genre_id FROM genres_movies, "
				+ query + "WHERE movies2.movie_id=genres_movies.movie_id";
		if (movie.getGenres() != null) {
			for (Genre genre : movie.getGenres()) {
				query += " AND "
						+ genresToInt(genre)
						+ " IN (SELECT genre_id FROM genres_movies WHERE movie_id=movies2.movie_id)";
			}
		}
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		ResultSet rs = st.executeQuery(query);
		Mconn.setAvailability(true);

		return ResultSetToMovieDBwithoutDrinks(rs);
	}

	// gets a result set from the function findMovieWithDrink and builds a map
	// that contains all the information in the result set received with their
	// movie id
	private static Map<Integer, MovieDB> ResultSetToMovieDBwithDrinks(
			ResultSet rs) throws SQLException {
		Map<Integer, MovieDB> result = new HashMap<Integer, MovieDB>();
		Rating rating;
		int stars, ratingInt, year, movie_id;
		String country, name;
		Genre genre;
		MovieDB movie;
		while (rs.next()) {
			movie_id = rs.getInt("movie_id");
			genre = IntToGenre(rs.getInt("genre_id"));
			if (result.containsKey(movie_id))
				result.get(movie_id).addGenre(genre);
			else {
				name = rs.getString("movie_name");
				stars = rs.getInt("stars");
				year = rs.getInt("year");
				country = rs.getString("country");
				ratingInt = rs.getInt("drink_id");
				if (ratingInt == 1)
					rating = Rating.ADULTS;
				else if (ratingInt == 2)
					rating = Rating.TEENS;
				else
					rating = Rating.KIDS;
				movie = new MovieDB(name, stars, country, year, rating, true);
				movie.addGenre(genre);
				result.put(movie_id, movie);
			}
		}
		return result;

	}

	// gets a result set from the function findMovieWithoutDrink and builds a
	// map
	// that contains all the information in the result set received with the
	// movie id for each movie
	private static Map<Integer, MovieDB> ResultSetToMovieDBwithoutDrinks(
			ResultSet rs) throws SQLException {
		Map<Integer, MovieDB> result = new HashMap<Integer, MovieDB>();
		int stars, year, movie_id;
		String country, name;
		MovieDB movie;
		Genre genre;
		while (rs.next()) {
			movie_id = rs.getInt("movie_id");
			genre = IntToGenre(rs.getInt("genre_id"));
			if (result.containsKey(movie_id))
				result.get(movie_id).addGenre(genre);
			else {
				name = rs.getString("movie_name");
				stars = rs.getInt("stars");
				year = rs.getInt("year");
				country = rs.getString("country");
				movie = new MovieDB(name, stars, country, year, null, false);
				movie.addGenre(genre);
				result.put(rs.getInt("movie_id"), movie);
			}
		}
		return result;

	}

	// deletes an actor from the database
	public static void deleteActor(int actor_id) throws SQLException {
		String query = "DELETE FROM actors WHERE actor_id=" + actor_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// deletes a movie from the database
	public static void deleteMovie(int movie_id) throws SQLException {
		String query = "DELETE FROM movies WHERE movie_id=" + movie_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// deletes a tv show from the database
	public static void deleteTVshow(int tv_id) throws SQLException {
		String query = "DELETE FROM tv_shows WHERE tv_id=" + tv_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// adds a new connection between an actor and a movie (checks before this
	// connection soesn't already exists)
	public static void addActorToMovie(int actor_id, int movie_id)
			throws SQLException {
		ManagedConnection Mconn = getConnection();
		if (checkActorMovieExists(actor_id, movie_id, Mconn)) {
			String query = "INSERT INTO actors_movies (actor_id, movie_id) VALUES ('"
					+ actor_id + "', '" + movie_id + "')";
			Mconn.getStatement().execute(query);
		}
		Mconn.setAvailability(true);

	}

	// checks if the connection between an actor and a movie already exists in
	// the system so we won't enter it twice
	private static boolean checkActorMovieExists(int actor_id, int movie_id,
			ManagedConnection Mconn) throws SQLException {
		String query = "SELECT * FROM actors_movies WHERE actor_id=" + actor_id
				+ " AND movie_id=" + movie_id;
		ResultSet rs = Mconn.getStatement().executeQuery(query);
		while (rs.next()) {
			return false;
		}
		return true;
	}

	// removes a connection between an actor and a movie
	public static void removeActorToMovie(int actor_id, int movie_id)
			throws SQLException {
		String query = "DELETE FROM actors_movies WHERE movie_id=" + movie_id
				+ " AND actor_id=" + actor_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// gets movie_id and returns a map of all the actors in that movie
	public static Map<Integer, String> getActorsForMovies(int movie_id)
			throws SQLException {
		String query = "SELECT actors.actor_id,actor_name FROM actors,actors_movies WHERE movie_id="
				+ movie_id + " AND actors.actor_id=actors_movies.actor_id";
		ManagedConnection Mconn = getConnection();
		ResultSet rs = Mconn.getStatement().executeQuery(query);
		Mconn.setAvailability(true);

		return ResultSetToActors(rs);

	}

	// edits the information of an actor
	public static void editActor(int actor_id, String name) throws SQLException {
		String query = "UPDATE actors SET actor_name='"
				+ Utils.escapeApostrophe(name) + "' WHERE actor_id=" + actor_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// edits the information of a tv show
	public static void editTVshow(int tv_id, TVshowDB show) throws SQLException {
		String query = "UPDATE tv_shows SET tv_name='"
				+ Utils.escapeApostrophe(show.getImdbName()) + "', stars="
				+ show.getStars() + ", decade=" + DecadetoInt(show.getDecade())
				+ " WHERE tv_id=" + tv_id;
		ManagedConnection Mconn = getConnection();
		Mconn.getStatement().execute(query);
		Mconn.setAvailability(true);
	}

	// editing all the information on the movie
	public static void editMovie(int movie_id, MovieDB movie)
			throws SQLException {
		String query = "UPDATE movies SET movie_name='"
				+ Utils.escapeApostrophe(movie.getImdbName()) + "', stars="
				+ movie.getStars() + ", country='"
				+ Utils.escapeApostrophe(movie.getCountry()) + "', year="
				+ movie.getYear() + " WHERE movie_id=" + movie_id;
		ManagedConnection Mconn = getConnection();
		Statement st = Mconn.getStatement();
		st.execute(query);

		// updating the drink "situation" for the movie
		if (movie.getIsDrink() == true && movie.getRating() != null) {
			query = "UPDATE movies_drinks SET drink_id="
					+ RatingtoInt(movie.getRating()) + " WHERE movie_id="
					+ movie_id;
			st.execute(query);
		} else {
			query = "DELETE FROM movies_drinks WHERE movie_id=" + movie_id;
			st.execute(query);
		}
		// deleting all the genres and then adding all the genres again
		query = "DELETE FROM genres_movies WHERE movie_id=" + movie_id;
		st.execute(query);

		PreparedStatement pstmt = Mconn
				.prepareStatement("INSERT INTO genres_movies(movie_id,genre_id) VALUES(?,?)");
		Mconn.setAutoCommit(false);

		if (movie.getGenres() != null) {
			for (Genre genre : movie.getGenres()) {
				pstmt.setInt(1, movie_id);
				pstmt.setInt(2, genresToInt(genre));
				pstmt.addBatch();
			}
		}

		pstmt.executeBatch();
		Mconn.commit();
		Mconn.setAutoCommit(true);

		Mconn.setAvailability(true);
	}

	// gets an enum decade and returns the int which represents this decade in
	// the database
	private static int DecadetoInt(Decade decade) {
		if (decade == Decade.OLD)
			return 1;
		if (decade == Decade.MID)
			return 2;
		return 3;
	}

	// gets an enum rating and returns the int which represents this rating in
	// the database
	private static int RatingtoInt(Rating rating) {
		if (rating == Rating.ADULTS)
			return 1;
		if (rating == Rating.TEENS)
			return 2;
		return 3;
	}

	// returns a result set of the column from the table
	public static ResultSet getFullTable(String table, String column)
			throws SQLException {

		String query = "SELECT " + column + " FROM " + table;
		ManagedConnection Mconn = getConnection();
		ResultSet rs = Mconn.getStatement().executeQuery(query);
		Mconn.setAvailability(true);

		return rs;

	}

	// finds the new actors in that should be added to the database
	public static List<String> newActors(List<String> actors, int first_actress)
			throws SQLException {
		ManagedConnection Mconn = getConnection();
		ResultSet rs;
		Statement st = Mconn.getStatement();
		List<String> new_actors = new ArrayList<String>();
		int i;
		String original_name;

		for (i = 0; i < first_actress; i++) {
			original_name = actors.get(i);
			rs = st.executeQuery("SELECT actor_id FROM actors WHERE actor_imdb_name='"
					+ Utils.escapeApostrophe(original_name) + "'");
			if (!rs.next())
				new_actors.add(original_name);
		}

		Mconn.setAvailability(true);

		return new_actors;
	}

	// finds the new actresses in that should be added to the database
	public static List<String> newActresses(List<String> actors,
			List<String> new_actors, int first_actress) throws SQLException {
		ManagedConnection Mconn = getConnection();
		ResultSet rs;
		Statement st = Mconn.getStatement();
		int i;
		String original_name;

		for (i = first_actress; i < actors.size(); i++) {
			original_name = actors.get(i);
			rs = st.executeQuery("SELECT actor_id FROM actors WHERE actor_imdb_name='"
					+ Utils.escapeApostrophe(Utils.turnToFemale(original_name))
					+ "'");
			if (!rs.next())
				new_actors.add(original_name);
		}

		Mconn.setAvailability(true);

		return new_actors;
	}

	public static String getActorImdbName(String actor) throws SQLException {
		String query = "";
		query += "SELECT actor_imdb_name FROM actors WHERE actor_name = '"
				+  Utils.escapeApostrophe(actor).toLowerCase() + "'";

		ManagedConnection Mconn = getConnection();
		Statement statement = Mconn.getStatement();
		ResultSet resultSet = statement.executeQuery(query);

		Mconn.setAvailability(true);

		String res = "";
		resultSet.next();
		res = resultSet.getString("actor_imdb_name");

		return res;

	}

}
